﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Windows.Forms;

namespace Win_Oracle
{
    public class Oracle_Helper
    {
        public Oracle_Helper()
        {

        }

        public Oracle_Helper(string connstr)
        {
            ConnStr = connstr;
        }

        public string ConnStr { get; set; }

        private Oracle.ManagedDataAccess.Client.OracleConnection _conn = null;
        public OracleConnection CONN
        {
            get
            {
                if (String.IsNullOrEmpty(ConnStr) || _conn == null)
                {
                    try
                    {
                        _conn = new OracleConnection();
                        _conn.ConnectionString = ConnStr;
                    }
                    catch(Exception exc)
                    {
                        _conn = null;
                    }
                }
                //
                return _conn;
            }
        }

        public int RunNoQuery(string sql, List<OracleParameter> lists)
        {
            int i = -1;
            if (CONN != null)
            {
                OracleCommand comm = CONN.CreateCommand();
                comm.CommandText = sql;
                if (lists != null && lists.Count > 0)
                {
                    comm.Parameters.AddRange(lists.ToArray());
                }
                //
                try
                {
                    comm.Connection.Open();
                    //
                    i = comm.ExecuteNonQuery();
                    //
                }
                catch (Exception exc)
                {
                    MessageBox.Show(exc.Message);
                }
                finally
                {
                    comm.Connection.Close();
                }
            }
            return i;
        }

        public object RunScalar(string sql, List<OracleParameter> lists)
        {
            object obj = null;
            if (CONN != null)
            {
                OracleCommand comm = CONN.CreateCommand();
                comm.CommandText = sql;
                if (lists != null && lists.Count > 0)
                {
                    comm.Parameters.AddRange(lists.ToArray());
                }
                //
                try
                {
                    comm.Connection.Open();
                    //
                    obj = comm.ExecuteScalar();
                    //
                }
                catch (Exception exc)
                {
                    MessageBox.Show(exc.Message);
                }
                finally
                {
                    comm.Connection.Close();
                }
            }
            return obj;
        }

        public DataTable GetTable(string sql, List<OracleParameter> lists)
        {
            DataTable dt = new DataTable();
            if (CONN != null)
            {
                OracleCommand comm = CONN.CreateCommand();
                comm.CommandText = sql;
                if (lists != null && lists.Count > 0)
                {
                    comm.Parameters.AddRange(lists.ToArray());
                }
                //
                try
                {
                    comm.Connection.Open();
                    //
                    var reader = comm.ExecuteReader();
                    dt.Load(reader);
                    //
                }
                catch (Exception exc)
                {
                    MessageBox.Show(exc.Message);
                }
                finally
                {
                    comm.Connection.Close();
                }
            }
            return dt;
        }

    }
}
